package com.cham.video.mapper;

import com.cham.user.entity.User;
import com.cham.video.dto.VideoInfo;
import com.cham.video.entity.ChamVideo;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author Howl-Xu
 * @version 1.0.0
 * @Description: 请描述一下这个类文件吧
 * @createTime 2020年03月15日 09:53:00
 */
public interface VideoMapper {

    @Select("select\n" +
            "\ta.*,\n" +
            "\t(@videoIndex := @videoIndex + 1) videoIndex\n" +
            "from\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\tcv.*,\n" +
            "\t\tsu.nick_name\n" +
            "\tfrom\n" +
            "\t\tcham_video cv,\n" +
            "\t\tsys_user su\n" +
            "\twhere\n" +
            "\t\tcv.fenqu_parent = #{fenquParent}\n" +
            "\t\tand date_format(cv.create_date, '%Y-%m-%d') between date_format(DATE_SUB(now(), interval 7 day), '%Y-%m-%d') and date_format(now(), '%Y-%m-%d')\n" +
            "\t\tand su.user_uuid = cv.user_uuid\n" +
            "\torder by\n" +
            "\t\tcv.bofang desc\n" +
            "\tlimit 10 ) a,\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\t@videoIndex := 0) as videoIndex\n" +
            "order by\n" +
            "\tvideoIndex asc")
    List<VideoInfo> getVideoInfo7dayTop10ByFenquParent(String fenquParent);

    @Select("select\n" +
            "\tcv.*,\n" +
            "\tsu.nick_name \n" +
            "from\n" +
            "\tcham_video cv,\n" +
            "\tsys_user su\n" +
            "where\n" +
            "\tcv.fenqu_parent = #{fenquParent}\n" +
            "\tand cv.user_uuid = su.user_uuid \n" +
            "order by\n" +
            "\tcv.bofang desc\n" +
            "limit 12")
    List<VideoInfo> getVideoInfoTop12ByFenquParent(@Param("fenquParent") String fenquParent);


    @Select("select\n" +
            "\tcv.*,\n" +
            "\tsu.nick_name \n" +
            "from\n" +
            "\tcham_video cv,\n" +
            "\tsys_user su\n" +
            "where cv.fenqu_parent is not null and cv.fenqu_parent !='00'\n" +
            "and su.user_uuid = cv.user_uuid \n" +
            "order by\n" +
            "\tcv.bofang desc\n" +
            "limit 6")
    List<VideoInfo> getVideoInfoTop6();



    @Select("select\n" +
            "\ta.*,\n" +
            "\t(@videoIndex := @videoIndex + 1) videoIndex\n" +
            "from\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\tcv.*,\n" +
            "\t\tsu.nick_name\n" +
            "\tfrom\n" +
            "\t\tcham_video cv,\n" +
            "\t\tsys_user su\n" +
            "\twhere\n" +
            "\t\tcv.fenqu_parent =\n" +
            "\t\t#{fenquParent}\n" +
            "\t\tand date_format(cv.create_date, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d')\n" +
            "\t\tand su.user_uuid = cv.user_uuid\n" +
            "\torder by\n" +
            "\t\tcv.bofang desc\n" +
            "\tlimit 10 ) a,\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\t@videoIndex := 0) as videoIndex\n" +
            "order by\n" +
            "\tvideoIndex asc")
    List<VideoInfo> getVideoInfo1dayTop10ByFenquParent(String fenquParent);





    @Select("select\n" +
            "\ta.*,\n" +
            "\t(@videoIndex := @videoIndex + 1) videoIndex\n" +
            "from\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\tcv.*,\n" +
            "\t\tsu.nick_name\n" +
            "\tfrom\n" +
            "\t\tcham_video cv,\n" +
            "\t\tsys_user su\n" +
            "\twhere\n" +
            "\t\tcv.fenqu_parent =\n" +
            "\t\t#{fenquParent}\n" +
            "\t\tand date_format(cv.create_date, '%Y-%m-%d') between date_format(DATE_SUB(now(), interval 3 day), '%Y-%m-%d') and date_format(now(), '%Y-%m-%d')\n" +
            "\t\tand su.user_uuid = cv.user_uuid\n" +
            "\torder by\n" +
            "\t\tcv.bofang desc\n" +
            "\tlimit 10 ) a,\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\t@videoIndex := 0) as videoIndex\n" +
            "order by\n" +
            "\tvideoIndex asc")
    List<VideoInfo> getVideoInfo3dayTop10ByFenquParent(String fenquParent);








//    @Select("select\n" +
//            "\t(@videoIndex:=@videoIndex+1) videoIndex,\n" +
//            "\tcv.*\n" +
//            "from\n" +
//            "\tcham_video cv,\n" +
//            "\t(SELECT @videoIndex:=0) as videoIndex\n" +
//            "order by\n" +
//            "\tcv.bofang desc,\n" +
//            "\tvideoIndex asc\n" +
//            "limit 12")
//    List<VideoInfo> getXinfanTop12();

    @Select("select\n" +
            "\tcv.*\n" +
            "from\n" +
            "\tcham_video cv\n" +
            "where cv.fenqu_parent = '00'\n" +
            "order by\n" +
            "\tcv.create_date desc\n" +
            "limit 4")
    List<VideoInfo> getVideoInfoSpecial();


    @Select("select * from cham_video cv where cv.fenzu_id = #{fenzuId} order by cv.fenzu_index asc")
    List<VideoInfo> getVideoInfoByFenzuId(int fenzuId);



    @Select("select\n" +
            "\ta.*,\n" +
            "\t(@videoIndex := @videoIndex + 1) videoIndex\n" +
            "from\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\tcv.*,\n" +
            "\t\tsu.nick_name\n" +
            "\tfrom\n" +
            "\t\tcham_video cv,\n" +
            "\t\tsys_user su\n" +
            "\twhere\n" +
            "\t\tcv.fenqu_parent =\n" +
            "\t\t#{fenquParent} and su.user_uuid = cv.user_uuid order by rand() limit 10)\n" +
            " a,\n" +
            "\t\t(\n" +
            "\t\tselect\n" +
            "\t\t\t@videoIndex := 0) as videoIndex\n" +
            "\torder by\n" +
            "\t\tvideoIndex asc")
    List<VideoInfo> getVideoInfoRandom10ByFenquParent(String fenquParent);


    @Insert("insert into cham_video(video_path,user_uuid,video_name,img_220x125,video_title,video_summary,create_date,fenqu_parent,fenqu_child,dianzan,bofang,toubi,fenzu_id,fenzu_index,video_time_str) VALUES (#{videoPath},#{userUuid},#{videoName},#{videoCoverImg220x125},#{videoTitle},#{videoSummary},#{createDate},#{fenquParent},#{fenquChild},#{dianzan},#{bofang},#{toubi},#{fenzuId},#{fenzuIndex},#{videoTimeStr})")
    @Options(useGeneratedKeys=true, keyProperty="videoId", keyColumn="video_id") //该注解用于返回主键
    void saveVideoInfo(ChamVideo chamVideo);

    @Update("update\n" +
            "\tcham_video cv\n" +
            "inner join (\n" +
            "\tselect\n" +
            "\t\t(ifnull(cv1.bofang,\n" +
            "\t\t0)+ 1) bofang,cv1.video_id \n" +
            "\tfrom\n" +
            "\t\tcham_video cv1\n" +
            "\twhere\n" +
            "\t\tcv1.video_id = #{videoId}) cv2 set\n" +
            "\tcv.bofang = cv2.bofang\n" +
            "where\n" +
            "\tcv.video_id = cv2.video_id")
    void addBofangCount(int videoId);


    @Select("select count(*) from cham_video cv where cv.fenzu_id = #{fenzuId}")
    int getMaxFenzuIndexByFenzuId(int fenzuId);

    @Insert("insert into cham_video(video_path,user_uuid,video_name,img_220x125,img_80x80,video_title,video_summary,create_date,fenqu_parent,fenqu_child,dianzan,bofang,toubi,fenzu_id,fenzu_index,video_time_str) VALUES (#{videoPath},#{userUuid},#{videoName},#{videoCoverImg220x125},#{videoCoverImg80x80},#{videoTitle},#{videoSummary},#{createDate},#{fenquParent},#{fenquChild},#{dianzan},#{bofang},#{toubi},#{fenzuId},#{fenzuIndex},#{videoTimeStr})")
    @Options(useGeneratedKeys=true, keyProperty="videoId", keyColumn="video_id") //该注解用于返回主键
    void saveXinfanInfo(ChamVideo chamVideo);

    @Insert("insert into cham_video(video_path,user_uuid,video_name,img_470x200,video_title,video_summary,create_date,fenqu_parent,fenzu_id,fenzu_index,video_time_str) VALUES (#{videoPath},#{userUuid},#{videoName},#{videoCoverImg470x200},#{videoTitle},#{videoSummary},#{createDate},'00',#{fenzuId},#{fenzuIndex},#{videoTimeStr})")
    @Options(useGeneratedKeys=true, keyProperty="videoId", keyColumn="video_id") //该注解用于返回主键
    void saveSPVideoInfo(ChamVideo chamVideo);

    @Select("select\n" +
            "\tcv1.*\n" +
            "from\n" +
            "\tcham_video cv1\n" +
            "where\n" +
            "\t(cv1.fenqu_parent,\n" +
            "\tcv1.fenzu_id,\n" +
            "\tcv1.fenzu_index) in (\n" +
            "\tselect\n" +
            "\t\tcv.fenqu_parent,\n" +
            "\t\tcv.fenzu_id,\n" +
            "\t\tmax(cv.fenzu_index)\n" +
            "\tfrom\n" +
            "\t\tcham_video cv\n" +
            "\twhere\n" +
            "\t\tcv.fenqu_parent = '01'\n" +
            "\t\tand date_format(cv.create_date, '%Y-%m-%d') between date_format(DATE_SUB(now(), interval 90 day), '%Y-%m-%d') and date_format(now(), '%Y-%m-%d')\n" +
            "\tgroup by\n" +
            "\t\tcv.fenqu_parent,\n" +
            "\t\tcv.fenzu_id )")
    List<VideoInfo> getXinfanCurrentUpdate();

    @Select("select\n" +
            "\tcv.*,su.nick_name \n" +
            "from\n" +
            "\tcham_video cv,sys_user su\n" +
            "where\n" +
            "\tcv.fenqu_parent = #{fenquParent} and su.user_uuid = cv.user_uuid \n" +
            "order by\n" +
            "\tbofang desc\n" +
            "limit 4")
    List<VideoInfo> getJingxuanTop4Video(@Param("fenquParent") String fenquParent);

    @Select("select\n" +
            "\tcv.*,\n" +
            "\tsu.nick_name \n" +
            "from\n" +
            "\tcham_video cv,\n" +
            "\tsys_user su\n" +
            "where\n" +
            "\tcv.fenqu_parent = #{fenquParent} and cv.fenqu_child = #{fenquChild}\n" +
            "\tand cv.user_uuid = su.user_uuid \n" +
            "order by\n" +
            "\tcv.bofang desc\n" +
            "limit 12")
    List<VideoInfo> getVideoInfoTop12ByFenquParentAndChild(String fenquParent, String fenquChild);

    @Select("select\n" +
            "\tcv.*,\n" +
            "\tsu.nick_name \n" +
            "from\n" +
            "\tcham_video cv,\n" +
            "\tsys_user su\n" +
            "where\n" +
            "\tcv.fenqu_parent = #{fenquParent} and cv.fenqu_child = #{fenquChild}\n" +
            "\tand cv.user_uuid = su.user_uuid \n" +
            "order by\n" +
            "\tcv.bofang desc\n" +
            "limit #{start},#{pageSize}")
    List<VideoInfo> getVideoInfoFenyeByFenquParentAndChild(String fenquParent, String fenquChild, int start, int pageSize);

    @Select("select\n" +
            "\ta.*,\n" +
            "\t(@videoIndex := @videoIndex + 1) videoIndex\n" +
            "from\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\tcv.*,\n" +
            "\t\tsu.nick_name\n" +
            "\tfrom\n" +
            "\t\tcham_video cv,\n" +
            "\t\tsys_user su\n" +
            "\twhere\n" +
            "\t\tcv.fenqu_parent = #{fenquParent} and cv.fenqu_child = #{fenquChild}\n" +
            "\t\tand date_format(cv.create_date, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d')\n" +
            "\t\tand su.user_uuid = cv.user_uuid\n" +
            "\torder by\n" +
            "\t\tcv.bofang desc\n" +
            "\tlimit 10 ) a,\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\t@videoIndex := 0) as videoIndex\n" +
            "order by\n" +
            "\tvideoIndex asc")
    List<VideoInfo> getVideoInfo1dayTop10ByFenquParentAndChild(String fenquParent, String fenquChild);

    @Select("select\n" +
            "\ta.*,\n" +
            "\t(@videoIndex := @videoIndex + 1) videoIndex\n" +
            "from\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\tcv.*,\n" +
            "\t\tsu.nick_name\n" +
            "\tfrom\n" +
            "\t\tcham_video cv,\n" +
            "\t\tsys_user su\n" +
            "\twhere\n" +
            "\t\tcv.fenqu_parent =\n" +
            "\t\t#{fenquParent} and cv.fenqu_child = #{fenquChild}\n" +
            "\t\tand date_format(cv.create_date, '%Y-%m-%d') between date_format(DATE_SUB(now(), interval 3 day), '%Y-%m-%d') and date_format(now(), '%Y-%m-%d')\n" +
            "\t\tand su.user_uuid = cv.user_uuid\n" +
            "\torder by\n" +
            "\t\tcv.bofang desc\n" +
            "\tlimit 10 ) a,\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\t@videoIndex := 0) as videoIndex\n" +
            "order by\n" +
            "\tvideoIndex asc")
    List<VideoInfo> getVideoInfo3dayTop10ByFenquParentAndChild(String fenquParent, String fenquChild);

    @Select("select\n" +
            "\ta.*,\n" +
            "\t(@videoIndex := @videoIndex + 1) videoIndex\n" +
            "from\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\tcv.*,\n" +
            "\t\tsu.nick_name\n" +
            "\tfrom\n" +
            "\t\tcham_video cv,\n" +
            "\t\tsys_user su\n" +
            "\twhere\n" +
            "\t\tcv.fenqu_parent = #{fenquParent} and cv.fenqu_child = #{fenquChild}\n" +
            "\t\tand date_format(cv.create_date, '%Y-%m-%d') between date_format(DATE_SUB(now(), interval 7 day), '%Y-%m-%d') and date_format(now(), '%Y-%m-%d')\n" +
            "\t\tand su.user_uuid = cv.user_uuid\n" +
            "\torder by\n" +
            "\t\tcv.bofang desc\n" +
            "\tlimit 10 ) a,\n" +
            "\t(\n" +
            "\tselect\n" +
            "\t\t@videoIndex := 0) as videoIndex\n" +
            "order by\n" +
            "\tvideoIndex asc")
    List<VideoInfo> getVideoInfo7dayTop10ByFenquParentAndChild(String fenquParent, String fenquChild);

    @Select("select count(1) from cham_video cv where cv.fenqu_parent = #{fenquParent} and cv.fenqu_child = #{fenquChild}")
    int getCountOfVideoByFenquParentAndChild(String fenquParent, String fenquChild);
}
